package com.wanxi.dao.impl;

import com.wanxi.dao.UserDao;
import com.wanxi.model.UserModel;
import com.wanxi.util.GetSqlSession;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * dao层和数据库打交道：
 * 要和数据库进行连接，增删改查
 * 无论哪个对象都要做增删改查，所以应该有一个工具类去调用
 */
public class UserImpl implements UserDao {
    @Override
    public int enable(UserModel model) {
        return 0;
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`," +
                " `email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + userModel.getName() + "', '" + userModel.getAddress() + "', now()," +
                " '" + userModel.getEmail() + "', 1, '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', sysdate(), " +
                "'" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');\n";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel model) {
//        String sql = "update user set ";
//        if (!"".equals(model.getName())) {
//            sql += "username='" + model.getName() + "',";
//        }
//        if (!"".equals(model.getAddress())) {
//            sql += "address='" + model.getAddress() + "',";
//        }
//        if (!"".equals(model.getEmail())) {
//            sql += "email='" + model.getEmail() + "', ";
//        }
//        if (model.getEnable() != -1) {
//            sql += "enable=" + model.getEnable() + ",";
//        }
//
//        if (!"".equals(model.getPhone())) {
//            sql += "phone='" + model.getPhone() + "', ";
//        }
//
//        if (!"".equals(model.getSex())) {
//            sql += "sex='" + model.getSex() + "', ";
//        }
//        if (!"".equals(model.getHobby())) {
//            sql += "hobby='" + model.getHobby() + "', ";
//        }
//        if (!"1900-01-01".equals(model.getBirthday())) {
//            sql += "birthday='" + model.getBirthday() + "', ";
//        }
////        update_time一定要写在最后 where之前
//        sql += " update_time=now()";
////        sql += " remark ='' ";
//        sql += "where id=" + model.getId();
//        return JDBC.update(sql);
        return GetSqlSession.getSqlSession().update("UserDao.update", model);
    }

    @Override
    public int delete(UserModel model) {
        String sql = "delete from user  where id=" + model.getId();
        return JDBC.update(sql);
//        return GetSqlSession.getSqlSession().delete("UserDao.delete", model);
    }

    @Override
    public UserModel findById(UserModel model) {
        String sql = "select * from user where id=" + model.getId();
        ResultSet query = JDBC.query(sql);
//        UserModel userModel = new UserModel();
//        1.有可能query这个为null，则直接返回userModel
//        2.如果有数据，则解析里面的数据封装到userModel
//        try {
//            while (query.next()) {
//                userModel.setId(query.getInt("id"));
//                userModel.setName(query.getString("username"));
//                userModel.setAddress(query.getString("address"));
//                userModel.setPhone(query.getString("phone"));
//                userModel.setBirthday(query.getString("birthday"));
//                userModel.setHobby(query.getString("hobby"));
//                userModel.setEmail(query.getString("email"));
//                userModel.setEnable(query.getInt("enable"));
//                userModel.setCreateTime(query.getString("create_time"));
//                userModel.setUpdateTime(query.getString("update_time"));
//                userModel.setSex(query.getString("sex"));
//            }
//        } catch (Exception e) {
//
//        }
//        return userModel;
        List<UserModel> list = getUserData(query);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public List<UserModel> findAll(UserModel model) {
        String sql = "select " +
                "        id, " +
                "        username," +
                "        phone," +
                "        sex," +
                "        email," +
                "        date_format(birthday,'%Y-%m-%d') birthday," +
                "        hobby," +
                "        enable," +
                "        address," +
                "        create_time," +
                "        update_time " +
                "from user where 1=1 ";//1=1 可以换成true
//        List<Object> sqlList = new ArrayList<>();
//        if (!"".equals(model.getName())) {
////            sql += " and username like '%" + model.getName() + "%' ";
//            sql += " and username like '%?%' ";
//            sqlList.add(model.getName());
//        }
//        if (!"".equals(model.getAddress())) {
////            sql += " and address like '%" + model.getAddress() + "%' ";
//            sql += " and address like '%?%' ";
//            sqlList.add(model.getAddress());
//        }
////        如果开始生日为默认值：结束生日为默认值，则查询所有生日
////        如果开始生日为默认值：结束生日不为默认值，则查询小于结束生日的日期
////        如果开始生日不为默认值：结束生日为默认值，则查询大于开始生日的日期
////        如果开始生日不为默认值：结束生日不为默认值，则查询开始和结束之间的日期
//
//        if (!"1900-01-01".equals(model.getBirthdayStart())) {
////            sql += " and birthday >='" + model.getBirthdayStart() + "' ";
//            sql += " and birthday >=? ";
//            sqlList.add(model.getBirthdayStart());
//        }
//        if (!"1900-01-01".equals(model.getBirthdayEnd())) {
////            sql += " and birthday <='" + model.getBirthdayEnd() + "' ";
//            sql += " and birthday <=? ";
//            sqlList.add(model.getBirthdayEnd());
//        }
//        /*if (!"1900-01-01".equals(model.getBirthdayStart()) && "1900-01-01".equals(model.getBirthdayEnd())) {
//            sql += " and birthday >='" + model.getBirthdayStart() + "' ";
//        }
//
//        else if ("1900-01-01".equals(model.getBirthdayStart()) && !"1900-01-01".equals(model.getBirthdayEnd())) {
//            sql += " and birthday <='" + model.getBirthdayEnd() + "' ";
//        } else if (!"1900-01-01".equals(model.getBirthdayStart()) && !"1900-01-01".equals(model.getBirthdayEnd())) {
//            sql += " and birthday between '" + model.getBirthdayStart() + "' and '" + model.getBirthdayEnd() + "'";
//        }*/
//        if (model.getEnable() != null && model.getEnable() != -1) {
//            sql += " and enable=?";
//            sqlList.add(model.getEnable());
//        }
        sql += getWhereSql(model);
        sql += " order by id desc ";
        sql += " limit " + (model.getPage() - 1) * model.getLimit() + "," + model.getLimit() + "";
        ResultSet query = JDBC.query(sql);
        return getUserData(query);
//        return GetSqlSession.getSqlSession().selectList("UserDao.findAll", model);
    }

    private List<UserModel> getUserData(ResultSet query) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (query.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(query.getInt("id"));
                userModel.setName(query.getString("username"));
                userModel.setAddress(query.getString("address"));
                userModel.setPhone(query.getString("phone"));
                userModel.setBirthday(query.getString("birthday"));
                userModel.setHobby(query.getString("hobby"));
                userModel.setEmail(query.getString("email"));
                userModel.setEnable(query.getInt("enable"));
                userModel.setCreateTime(query.getString("create_time"));
                userModel.setUpdateTime(query.getString("update_time"));
                userModel.setSex(query.getString("sex"));
                list.add(userModel);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 得到总条数一般情况下和findAll的查询条件一致
     *
     * @param model
     * @return
     */
    @Override
    public int count(UserModel model) {
        String sql = "select count(*) count from user where 1=1 ";
        sql += getWhereSql(model);
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return query.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel model) {
        String sql = "select * from user where username='" + model.getName() + "' and password='" + model.getPassword() + "'";
        ResultSet query = JDBC.query(sql);
        UserModel userModel = null;
        try {
            while (query.next()) {
                userModel = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public int updatePassword(UserModel model) {
        String sql = "update user set password='" + model.getPassword() + "' where id=" + model.getId();
        return JDBC.update(sql);
    }

    private String getWhereSql(UserModel model) {
        String sql = "";
        if (!"".equals(model.getName())) {
            sql += " and username like '%" + model.getName() + "%' ";
        }
        if (!"".equals(model.getAddress())) {
            sql += " and address like '%" + model.getAddress() + "%' ";
        }
//        如果开始生日为默认值：结束生日为默认值，则查询所有生日
//        如果开始生日为默认值：结束生日不为默认值，则查询小于结束生日的日期
//        如果开始生日不为默认值：结束生日为默认值，则查询大于开始生日的日期
//        如果开始生日不为默认值：结束生日不为默认值，则查询开始和结束之间的日期

        if (!"1900-01-01".equals(model.getBirthdayStart())) {
            sql += " and birthday >='" + model.getBirthdayStart() + "' ";
        }
        if (!"1900-01-01".equals(model.getBirthdayEnd())) {
            sql += " and birthday <='" + model.getBirthdayEnd() + "' ";
        }
        /*if (!"1900-01-01".equals(model.getBirthdayStart()) && "1900-01-01".equals(model.getBirthdayEnd())) {
            sql += " and birthday >='" + model.getBirthdayStart() + "' ";
        }

        else if ("1900-01-01".equals(model.getBirthdayStart()) && !"1900-01-01".equals(model.getBirthdayEnd())) {
            sql += " and birthday <='" + model.getBirthdayEnd() + "' ";
        } else if (!"1900-01-01".equals(model.getBirthdayStart()) && !"1900-01-01".equals(model.getBirthdayEnd())) {
            sql += " and birthday between '" + model.getBirthdayStart() + "' and '" + model.getBirthdayEnd() + "'";
        }*/
        if (model.getEnable() != null && model.getEnable() != -1) {
            sql += " and enable=" + model.getEnable();
        }
        return sql;
    }
}
